#!/usr/bin/env python
# encoding=utf8

## Database: status error codes
#  0 : not parsed
#  1 : parsed correctly
#  2 : something went wrong

import sys
import sqlite3
import twitter
from datetime import datetime
from time import sleep
import time

db = 'twitter_user_friendlists.sqlite'

api = twitter.Api(consumer_key ='', consumer_secret='', access_token_key= '',  access_token_secret= '')


def queryDb(db):

    try:
        conn = sqlite3.connect(db)
        cursor = conn.cursor()
        cursor.execute("SELECT twt_id FROM log WHERE status = 0")
    except Exception,e:
        print str(e)

    query_list = []
    
    for (twt_id,) in cursor.fetchall():
        query_list.append(twt_id.encode("utf-8"))

    return query_list

def insertFriendlistDb(db, user, friendlist):

    # Prepare timestamp
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    try:
        conn = sqlite3.connect(db)
        cursor = conn.cursor()
        for friend in friendlist:
            cursor.execute("INSERT OR IGNORE INTO friendlist ([from],[to],timestamp) VALUES (?,?,?);", (user, friend, now))
        conn.commit()    

        return

    except Exception,e:
        print str(e)

        return

def insertUserDb(db, obj):

    # Prepare timestamp
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    try:
        conn = sqlite3.connect(db)
        cursor = conn.cursor()
        for user in obj:
            cursor.execute("INSERT OR IGNORE INTO friendlist (id, name, screen_name, location, description, default_profile, default_profile_image, profile_image_url, profile_background_tile, profile_background_image_url, profile_banner_url, profile_sidebar_fill_color, profile_background_color, profile_link_color, profile_text_color, protected, utc_offset, time_zone, url, statuses_count, followers_count, friends_count, favourites_count, geo_enabled, verified, lang, notifications, contributors_enabled, created_at, listed_count, timestamp) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", (user['id'], user['name'], user['screen_name'], user['location'], user['description'], user['default_profile'], user['default_profile_image'], user['profile_image_url'], user['profile_background_tile'], user['profile_background_image_url'], user['profile_banner_url'], user['profile_sidebar_fill_color'], user['profile_background_color'], user['profile_link_color'], user['profile_text_color'], user['protected'], user['utc_offset'], user['time_zone'], user['url'], user['statuses_count'], user['followers_count'], user['friends_count'], user['favourites_count'], user['geo_enabled'], user['verified'], user['lang'], user['notifications'], user['contributors_enabled'], user['created_at'], user['listed_count'], now))
        conn.commit()    

        return

    except Exception,e:
        print str(e)

        return

    
def updateLog(db, user, status, n_friends=None):

    # Prepare timestamp
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    try:
        conn = sqlite3.connect(db)
        cursor = conn.cursor()
        cursor.execute("UPDATE log SET status=?, timestamp=?, n_friends=? WHERE twt_id = ?;", (status, now, n_friends, user))
        conn.commit()    

        return

    except Exception,e:
        print str(e)

        return

    
def main(db, api):

    query_list = queryDb(db)
    counter = 1
    start = int(time.time())
    for user in query_list:
        print(counter)
        if counter>15:
            while True:
                if int(time.time()) < start + 905:
                    sleep(5)
                else:
                    counter = 1
                    start = int(time.time())
                    break
        try:
            friendlist = api.GetFriendIDs(user_id=user, count=5000)
            insertFriendlistDb(db, user, friendlist)
            updateLog(db, user, status=0, n_friends=len(friendlist))
            counter += 1
        except twitter.error.TwitterError, e:
            print str(e)
            sleep(900)
            main(db, api)
        except Exception, e:
            print str(e)
            sys.exit()

    return


main(db, api)
        
